'''
@Description: file content
@Author: 陈广兴(Chen Guang Xing)
@Date: 2019-10-29 08:39:37
@LastEditors: 陈广兴(Chen Guang Xing)
@LastEditTime: 2019-10-30 16:09:50
'''


import sys # 读命令行参数 for CCmdLine 
import re # 正则表达式
import configparser # 读ini文件 for CIniFile
import os # for CIniFile
import pymysql # for CMySQL


# re.compile("^USE.*") 找到所有的USE 

'''
存在的问题
    1. MySQL不支持数据库的COMMENT. 需要另一个文件保存.目前数据库少还行,如果数据库多了就不好维护了.
    2. 代码中很多地方耦合比较强,有机会应该根据使用中出现的问题优化一下.
    
优化方案
    1. 使用脚注?
    
数据结构
    需要保存的数据有:
    数据库下有哪些表, 表属性, 表下有哪些字段,字段属性和注释
    dicDB = {数据库名:{表名:表属性}}
    dicTable = {数据库+表名:{字段名:字段属性+注释}}
    
文档格式
    格式1: 
        # 数据库名
        ## 表名
        ### 字段名
    
    格式2: 目前选择这个.
        # 数据库名
        ## 表名
        | 字段名 | 字段属性 | 字段注释 |
        
SQL里增加格式
    1. 表注释里的前两个|之间是表标题. 例如 |体检化验单| , 对应marakdown里的 ## laboratorys 体检化验单
    2. 字段注释里的 @数据库.表.字段 说明该字段意义与某字段相同. 这个目前在markdown里没有体现, 以后可以做一个统计便于使用时查阅. 
'''
# todo 1. 测试其它数据库管理器导出的SQL文件是否好用? 并记录导出SQL文件规则.
# todo 2. 可选从SQL文件或从数据库导出文档
# todo 3. 如果没有ini文件则自动生成
# todo 4. ini文件的前3行用于注释, 说明[]及title和comment的用法. 尤其是comment的字符串里面用<br>代替回车
# todo 5. 有 front.md 和 behind.md , front.md用于文档标题,简介等信息. behind.md用于附注等信息,很多表注释或者字段注释中放不下的内容可以通过markdown内链到附注.

'''
0. 简介
    CCmdLine用于处理命令行输入, CMySQL和CSQLFile用于将注释读到结构体, CMarkdown将结构体和ini写到Markdown文件, 
    CIniFile用于提供数据库的相关注释.

1. 命令行
    -f 指定SQL文件
    -h 指定MySQL服务器地址
    -u 指定MySQL服务器用户名
    -p 指定MySQL服务器密码
    -P 指定MySQL服务器端口
    -bdb 数据库黑名单
    -btb 表黑名单
    -wdb 数据库白名单
    -wtb 数据表白名单
'''

class Const():
    class ConstError(TypeError): pass

    class ConstCaseError(ConstError): pass

    def __setattr__(self, key, value):
        if key in self.__dict__.keys():
            # 存在性验证
            raise self.ConstError("Can't change a const variable: '%s'" % key)

        if not key.isupper():
            # 语法规范验证
            raise self.ConstCaseError("Const variable must be combined with upper letters:'%s'" % key)

        self.__dict__[key] = value

class CMySQL:
    __DBInfo = None     # dicDB = {数据库名:{表名:表属性}}
    __TableInfo = {}    # dicTable = {数据库+表名:{字段名:字段属性+注释}}
    __Ver = None
    __DBNameBlackList = []#('information_schema', 'mysql', 'performance_schema', 'sys', 'test', 'phpmyadmin',)
    __TblNameBlackList = []#('information_schema', 'mysql', 'performance_schema', 'sys', 'test', 'phpmyadmin',)
    __conn = None
    __cur  = None

    def __init__( self, dicLoginInfo ):
        self.__conn = pymysql.connect(host=dicLoginInfo[Const.HOST], user=dicLoginInfo[Const.USER], passwd=dicLoginInfo[Const.PASS],port=int(dicLoginInfo[Const.PORT]))
        self.__cur  = self.__conn.cursor()
        if Const.BLACK_DB in dicLoginInfo:
            self.__DBNameBlackList = dicLoginInfo[Const.BLACK_DB]
        if Const.BLACK_TABLE in dicLoginInfo:
            self.__TblNameBlackList = dicLoginInfo[Const.BLACK_TABLE]

    def MySQL2SQL( self, strSQLFile ):
        self.__DBInfo = {}
        self.__cur.execute("SHOW DATABASES;")
        for line in self.__cur:
            for DBName in line:
                if DBName in self.__DBNameBlackList:
                    continue
                self.__DBInfo[DBName]=[]
        
        
        for strDBName in self.__DBInfo:
            self.__cur.execute("USE %s"%strDBName)
            self.__cur.execute("SHOW TABLES")
            for line in self.__cur:
                for TableName in line:
                    if TableName in self.__TblNameBlackList:
                        continue
                    self.__DBInfo[strDBName].append(TableName)

        #print(self.__DBInfo)
        #return 
        with open( strSQLFile, 'w',encoding='UTF-8' ) as objFile:
            for strDBName in self.__DBInfo:
                objFile.write( "USE `"+strDBName+"`;\n\n")
                for strTableName in self.__DBInfo[strDBName]:
                    self.__cur.execute("USE %s"%strDBName)
                    self.__cur.execute("SHOW CREATE TABLE `"+strTableName+"`; ")
                    for line in self.__cur:
                        objFile.write(line[1]+';\n\n')
    
    def Clear(self):
        self.__DBInfo = None
        self.__TableInfo = {}
        self.__Ver = None

    def GetDBInfo(self):
        return self.__DBInfo
    def GetTableInfo(self):
        return self.__TableInfo
        
    # 析构不忘关闭句柄
    def __del__(self):
        if None != self.__cur:
            self.__cur.close()
        if None != self.__conn:
            self.__conn.close()

    # 获得MySQL版本, 例如: 5.6.44-log
    def GetVersion(self):
        if None!=self.__Ver:
            return self.__Ver
            
        self.__cur.execute("SELECT VERSION()") # ('5.7.17-log',)
        for line in self.__cur:
            if isinstance(line,tuple) and 1<=len(line):
                self.__Ver = line[0]
            else:
                self.__Ver = '获得数据库版本失败.'
        return self.__Ver

    # 获得MySQL有那些数据库
    def GetDataBases(self):
        self.__cur.execute("SHOW DATABASES")
        if isinstance(self.__DBInfo,dict):
            return self.__DBInfo.keys()
        
        self.__DBInfo = {}
        for line in self.__cur:
            for DBName in line:
                if DBName in self.__DBNameBlackList:
                    continue
                self.__DBInfo[DBName]=None
        return self.__DBInfo.keys()

    # 获得指定数据库有哪些表格
    def GetTables(self, DBName ):
        if DBName in self.__DBInfo.keys() and isinstance(self.__DBInfo[DBName],dict):
            return self.__DBInfo[DBName]
            
        self.__cur.execute("USE %s"%DBName)
        self.__cur.execute("SHOW TABLES")
        self.__DBInfo[DBName]={}
        for line in self.__cur:
            for TableName in line:
                self.__DBInfo[DBName][TableName]=None
        return self.__DBInfo[DBName]

    # 获得指定表有那些列
    def GetColumns(self, DBName, TableName ):
        #TableName = 'admins'
        #print("SHOW COLUMNS FROM %s"%TableName)
        self.__cur.execute("USE %s"%DBName)
        self.__cur.execute("SHOW COLUMNS FROM %s"%TableName)
        self.__DBInfo[DBName][TableName]={}
        for ColInfo in self.__cur:
            self.__DBInfo[DBName][TableName][ColInfo[0]] = []
            for field in ColInfo:
                if field==ColInfo[0]:
                    continue
                self.__DBInfo[DBName][TableName][ColInfo[0]].append(field)

    # 获得表约束
    def GetTablesInfo(self, DBName,TableName):
        #TableName = 'admins'
        self.__cur.execute("USE %s"%DBName)
        self.__cur.execute("SHOW CREATE TABLE %s"%TableName)
        #TableInfo = {}
        last = ''
        for line in self.__cur:
            for field in line:
                last = field
        begin = last.rfind(')')
        last = last[begin+2:-1]
        last = last.split()
        self.__TableInfo[TableName]=last

    def ProcessDB(self):
        pass
        #db = DBInfo()
        #print(self.GetVersion())
        #print(self.GetDataBases())
        #print(self.GetTables('c1'))
        #self.GetColumns('c1','followups')

class CSQLFile:
    dicDB = {}    # 格式: {数据库名:{表:表属性}}
    dicTable = {} # 格式: {数据库_表名:{字段:字段属性}}
    strCurrentDB = '' # 当前数据库
    strCurrentTable = '' # 当前表
    strCurrentLine = '' # SQL文件的当前行
    strSQLFile = '' # 输入的SQL文件名

    def __init__(self, strSQLFile):
        self.strSQLFile = strSQLFile
    
    # 从语句中找到 `USE 数据库名;` 并设置为当前数据库,创建数据库dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findDB( self, line ):
        objMatch = re.match( re.compile("^USE\s`(.*)`;"), line )
        if objMatch:
            self.strCurrentDB = objMatch.group(1)
            if self.strCurrentDB not in self.dicDB:
                self.dicDB[self.strCurrentDB] = {}
            return True
        return False

    # 从语句中找到 `CREATE TABLE 表名(` 并设置为当前表,创建表dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findTable( self, line ):
        objMatch = re.match( re.compile("CREATE\sTABLE\s`(.*)`\s\("), line )
        if objMatch:
            self.strCurrentTable = objMatch.group(1)
            if self.strCurrentTable not in self.dicDB[self.strCurrentDB]:
                self.dicDB[self.strCurrentDB][self.strCurrentTable] = ''
            if self.strCurrentDB+'_'+self.strCurrentTable not in self.dicTable:    
                self.dicTable[self.strCurrentDB+'_'+self.strCurrentTable] = {}
            return True
        return False

    # 从语句中找到 字段定义 并填入表dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findField( self, line ):
        line = line.strip()
        objMatch = re.match( re.compile("^`(.*),$"), line )
        if objMatch:
            line2 = objMatch.group(0)
            objMatch = re.match( re.compile("^`(.*)`"), line2 )
            if objMatch:
                strfindField = objMatch.group(1)
                strAttribute = line2[len(objMatch.group(0)):-1]
                strAttribute = strAttribute.strip()
                self.dicTable[self.strCurrentDB+'_'+self.strCurrentTable][strfindField]=strAttribute
                return True
        return False

    # 从语句中找到 表属性 并填入数据库dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findTableAttribute( self, line ):
        objMatch = re.match( re.compile("^\)(.*);"), line )
        if objMatch:
            self.dicDB[self.strCurrentDB][self.strCurrentTable] = objMatch.group(1)
            #print( self.dicDB )
            return True
        return False

    # 从SQL读数据库,数据表及表属性,字段及字段属性到dict结构体
    def ProcessSQLFile( self ):
        with open( self.strSQLFile, 'r',encoding='UTF-8' ) as objFile:
            line = True
            while line:
                line = objFile.readline()
                line = line.replace( 'CREATE TABLE IF NOT EXISTS','CREATE TABLE' )
                #if type(line)!=type(''):
                #    break
                    
                if self.findDB( line ):
                    continue
                    
                if self.findTable( line ):
                    continue
                    
                if self.findField( line ):
                    continue
                    
                if self.findTableAttribute(line):
                    continue
                
Const.INI_TITLE = 'Title'
Const.INI_COMMENT = 'Comment'
Const.INI_DEFAULT_SECTION = '注释'
Const.INI_DEFAULT_KEY1 = '关于小节'
Const.INI_DEFAULT_KEY2 = '关于title'
Const.INI_DEFAULT_KEY3 = '关于comment'
Const.INI_DEFAULT_VAL1 = '[db_name]代表数据库'
Const.INI_DEFAULT_VAL2 = 'title是数据库的中文名称'
Const.INI_DEFAULT_VAL3 = 'comment是对数据库的一段话介绍,如果需要换行使用<br>.'

class CIniFile:
    strPath = ''
    objIni = 0

    def __init__( self, strPath ):
        self.strPath = strPath
        self.objIni = configparser.ConfigParser()
        self.objIni.read(strPath)
        if not (os.path.exists(strPath) and os.path.isfile(strPath)):
            self.objIni.add_section( Const.INI_DEFAULT_SECTION )
            self.objIni.set( Const.INI_DEFAULT_SECTION, Const.INI_DEFAULT_KEY1, Const.INI_DEFAULT_VAL1)
            self.objIni.set( Const.INI_DEFAULT_SECTION, Const.INI_DEFAULT_KEY2, Const.INI_DEFAULT_VAL2)
            self.objIni.set( Const.INI_DEFAULT_SECTION, Const.INI_DEFAULT_KEY3, Const.INI_DEFAULT_VAL3)
            self.objIni.write(open(self.strPath, 'w'))

    def GetTitle(self, strDB, strDefault='' ):
        self.IfNotExist( strDB, Const.INI_TITLE)
        return self.objIni[strDB][Const.INI_TITLE]
        
    def GetComment(self, strDB, strDefault='' ):
        self.IfNotExist( strDB, Const.INI_COMMENT)
        return self.objIni[strDB][Const.INI_COMMENT]

    def IfNotExist(self, strDB, strKey):
        if strDB not in self.objIni.sections():
            self.objIni.add_section( strDB )
            self.objIni.set( strDB, strKey, '')
            self.objIni.write(open(self.strPath, 'w'))
            return

        if strKey not in self.objIni[strDB]:
            self.objIni.set( strDB, strKey, '')
            self.objIni.write(open(self.strPath, 'w'))

class CMarkdown:

    strMDFile = ''
    dicDB    = {} # 格式: {数据库名:{表:表属性}}
    dicTable = {} # 格式: {数据库_表名:{字段:字段属性}}
    objIni = 0
    
    def __init__(self, strMDFile, dicDB, dicTable, objIni ):
        self.strMDFile = strMDFile
        self.dicDB     = dicDB
        self.dicTable  = dicTable
        self.objIni    = objIni
        
    def CreateMarkdown( self ):
        strFront = ''
        strFileName = 'front.md'
        if os.path.exists(strFileName) and os.path.isfile(strFileName):
            with open( strFileName, 'r',encoding='UTF-8' ) as objFile:
                strFront = objFile.read()
        strBehind = ''
        strFileName = 'behind.md'
        if os.path.exists(strFileName) and os.path.isfile(strFileName):
            with open( strFileName, 'r',encoding='UTF-8' ) as objFile:
                strBehind = objFile.read()
                
        with open( self.strMDFile, 'w',encoding='UTF-8' ) as objFile:
            objFile.write( '[TOC]' + '\n' ) # Markdown的自动目录
            objFile.write( strFront )
            iDB = 1 # 章节号的章
            for strDB,dicTableInfo in self.dicDB.items(): # 遍历dicDB中所有的数据库
                # 写数据库名,中文名,数据注释
                strDBTitle,strDBComment = self.ProcessDBComment(strDB)
                objFile.write( '# {iDB} {strDB} {strDBTitle}\n'.format(iDB=iDB, strDB=strDB, strDBTitle=strDBTitle) )
                objFile.write( strDBComment )
                iTable = 1 # 章节号的节
                for strTable,strTableAttribute in dicTableInfo.items():
                    strTableAttribute = self.ProcessTableAttribute(strTableAttribute)
                    strTableAttribute, strTableTitle = self.GetTableTitle(strTableAttribute)
                    objFile.write( '## {iDB}.{iTable} {strTable} {strTableTitle}\n'.format(iDB=iDB, iTable=iTable, strTableTitle=strTableTitle, strTable=strTable)  )
                    objFile.write( strTableAttribute + '\n'  )
                    objFile.write( '|字段|属性|注释|' + '\n'  )
                    objFile.write( '| ---- | ---- | ---- |' + '\n'  )
                    for strField,strFieldAttribute in self.dicTable[strDB+'_'+strTable].items():
                        strFieldAttribute,strComment = self.ProcessFieldAttribute(strFieldAttribute)
                        objFile.write( '| '+strField+' | '+strFieldAttribute+' | '+strComment+' |' + '\n'  )
                    iTable += 1
                iDB += 1
            objFile.write( strBehind )
            
    def ProcessDBComment( self, strDB ):
        '''
        if strDB not in ini.sections():
            return strDB, ''
        strTitle = ''
        strComment = ''
        if 'Title'  in ini[strDB]:
            strTitle = ini.get( strDB, 'Title', )
        if 'Comment'  in ini[strDB]:
            strComment = ini.get( strDB, 'Comment' )
        return strTitle, strComment+'\n'
        '''
        strTitle   = self.objIni.GetTitle( strDB )
        strComment = self.objIni.GetComment( strDB )+'\n'
        return strTitle, strComment

    def ProcessTableAttribute( self, strTableAttribute ):
        strComment = ''
        strDeleteMe = ''
        objMatch = re.search( re.compile("COMMENT='(.*)'"), strTableAttribute )
        if objMatch:
            strComment = objMatch.group(1)
            strDeleteMe = objMatch.group(0)
            
        strResult = strTableAttribute.replace( strDeleteMe, '' )
        strResult = strResult.replace(' ','\n')
        strResult = strResult.replace('DEFAULT\nCHARSET','DEFAULT CHARSET') # DEFAULT CHARSET
        if 0<len(strComment):
            strResult += (strDeleteMe)
        return strResult

    def ProcessFieldAttribute( self, strFieldAttribute ):
        strComment = ''
        strDeleteMe = ''
        objMatch = re.search( re.compile("COMMENT\s'(.*)'"), strFieldAttribute )
        if objMatch:
            strComment = objMatch.group(1)
            strDeleteMe = objMatch.group(0)
        
        strResult = strFieldAttribute.replace( strDeleteMe, '' )
        strResult = strResult.replace(' ',',<br>')
        strResult = strResult.replace('NOT,<br>NULL','NOT NULL') # NOT NULL
        strResult = strResult.replace('DEFAULT,<br>','DEFAULT ') # DEFAULT ''
        strResult = strResult.replace('CHARACTER,<br>SET,<br>','CHARACTER SET ') # CHARACTER SET latin1 
        strResult = strResult.replace('COLLATE,<br>','COLLATE ') # COLLATE latin1_general_cs 
        return strResult,strComment

    def GetTableTitle(self, strTableAttribute):
        objMatch = re.search( re.compile("COMMENT='\[(.*)\]"), strTableAttribute )
        if objMatch:
            strTableAttribute = strTableAttribute.replace( objMatch.group(0), "COMMENT='" )
            return strTableAttribute, objMatch.group(1)
            
        return strTableAttribute, ''
            
Const.FILE = '-f'
Const.HOST = '-h'
Const.USER = '-u'
Const.PASS = '-p'
Const.PORT = '-P'
Const.BLACK_DB = '-bdb'
Const.BLACK_TABLE = '-btb'
Const.WHITE_DB = '-wdb'
Const.WHITE_TABLE = '-wtb'
Const.ERR_MSG = 'ErrMsg'
Const.ERR_MSG_WRONG_NUMBER_OF_ARGUMENTS = 'Wrong number of arguments'
# python sql2markdown.py -p contec2016 -bdb information_schema,mysql,performance_schema -btb caseid2019,cases2019,events2019,reportid2019,reports2019
# python sql2markdown.py -f new.sql
class CCmdLine:
    '''
    -f 指定SQL文件
    -h 指定MySQL服务器地址
    -u 指定MySQL服务器用户名
    -p 指定MySQL服务器密码
    -P 指定MySQL服务器端口
    -bdb 数据库黑名单
    -btb 表黑名单
    -wdb 数据库白名单
    -wtb 数据表白名单
    '''

    dicMsg = {Const.ERR_MSG:''}
    
    dicArg = {} # 命令行参数, 格式: { 开关:取值 }, 例如:{ '-f':'./xxx.sql' }

    def __init__(self):
        self.dicMsg[Const.ERR_MSG] = ''

        if  0==len(sys.argv)%2: 
            self.dicMsg[Const.ERR_MSG] = Const.ERR_MSG_WRONG_NUMBER_OF_ARGUMENTS
            return

        strKey =''
        for i in range(1,len(sys.argv)):
            if '-' == sys.argv[i][0]:
                strKey = sys.argv[i]
                self.dicArg[strKey] = []
                continue
            self.dicArg[strKey].append(sys.argv[i])

    def ProcessArg(self):
        lstWhiteDB = []
        lstWhiteTable = []
        lstBlackDB = []
        lstBlackTable = []

        if 0 != len(self.dicMsg[Const.ERR_MSG]):
            return self.dicMsg

        if Const.BLACK_DB in self.dicArg:
            self.dicMsg[Const.BLACK_DB] = self.dicArg[Const.BLACK_DB]
        
        if Const.BLACK_TABLE in self.dicArg:
            self.dicMsg[Const.BLACK_TABLE] = self.dicArg[Const.BLACK_TABLE]

        if Const.WHITE_TABLE in self.dicArg:
            self.dicMsg[Const.WHITE_TABLE] = self.dicArg[Const.WHITE_TABLE]
        
        if Const.WHITE_DB in self.dicArg:
            self.dicMsg[Const.WHITE_DB] = self.dicArg[Const.WHITE_DB]

        if Const.FILE in self.dicArg:
            self.dicMsg[Const.FILE] = self.dicArg[Const.FILE][0]
            return self.dicMsg

        if Const.HOST in self.dicArg:
            self.dicMsg[Const.HOST] = self.dicArg[Const.HOST][0]
        else: 
            self.dicMsg[Const.HOST] = '127.0.0.1'
        if Const.USER in self.dicArg:
            self.dicMsg[Const.USER] = self.dicArg[Const.USER][0]
        else:
            self.dicMsg[Const.USER] = 'root'
        if Const.PASS in self.dicArg:
            self.dicMsg[Const.PASS] = self.dicArg[Const.PASS][0]
        else:
            self.dicMsg[Const.PASS] = ''
        if Const.PORT in self.dicArg:
            self.dicMsg[Const.PORT] = self.dicArg[Const.PORT][0]
        else:
            self.dicMsg[Const.PORT] = '3306'
        return self.dicMsg

class CSql2Markdown:

    dicDB = {}    # 格式: {数据库名:{表:表属性}}
    dicTable = {} # 格式: {数据库_表名:{字段:字段属性}}
    
    strCurrentDB = '' # 当前数据库
    strCurrentTable = '' # 当前表
    strCurrentLine = '' # SQL文件的当前行
    
    strSQLFile = '' # 输入的SQL文件名
    strMDFile  = '' # 输出的MarkDown文件名

    def __init__(self):
        self.strSQLFile = sys.argv[1]
        self.strMDFile  = self.strSQLFile + '.md'
    
    # 从语句中找到 `USE 数据库名;` 并设置为当前数据库,创建数据库dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findDB( self, line ):
        objMatch = re.match( re.compile("^USE\s`(.*)`;"), line )
        if objMatch:
            self.strCurrentDB = objMatch.group(1)
            if self.strCurrentDB not in self.dicDB:
                self.dicDB[self.strCurrentDB] = {}
            return True
        return False

    # 从语句中找到 `CREATE TABLE 表名(` 并设置为当前表,创建表dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findTable( self, line ):
        objMatch = re.match( re.compile("CREATE\sTABLE\s`(.*)`\s\("), line )
        if objMatch:
            self.strCurrentTable = objMatch.group(1)
            if self.strCurrentTable not in self.dicDB[self.strCurrentDB]:
                self.dicDB[self.strCurrentDB][self.strCurrentTable] = ''
            if self.strCurrentDB+'_'+self.strCurrentTable not in self.dicTable:    
                self.dicTable[self.strCurrentDB+'_'+self.strCurrentTable] = {}
            return True
        return False
        
    # 从语句中找到 字段定义 并填入表dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findField( self, line ):
        line = line.strip()
        objMatch = re.match( re.compile("^`(.*),$"), line )
        if objMatch:
            line2 = objMatch.group(0)
            objMatch = re.match( re.compile("^`(.*)`"), line2 )
            if objMatch:
                strfindField = objMatch.group(1)
                strAttribute = line2[len(objMatch.group(0)):-1]
                strAttribute = strAttribute.strip()
                self.dicTable[self.strCurrentDB+'_'+self.strCurrentTable][strfindField]=strAttribute
                return True
        return False
        
    # 从语句中找到 表属性 并填入数据库dict
    # 输入: SQL文件中的一行字符串
    # 输出: True,找到; False,没找到.
    def findTableAttribute( self, line ):
        objMatch = re.match( re.compile("^\)(.*);"), line )
        if objMatch:
            
            self.dicDB[self.strCurrentDB][self.strCurrentTable] = objMatch.group(1)
            return True
        return False

    # 从SQL读数据库,数据表及表属性,字段及字段属性到dict结构体
    def ProcessSQLFile( self ):
        with open( self.strSQLFile, 'r',encoding='UTF-8' ) as objFile:
            line = True
            while line:
                line = objFile.readline()
                #if type(line)!=type(''):
                #    break
                    
                if self.findDB( line ):
                    continue
                    
                if self.findTable( line ):
                    continue
                    
                if self.findField( line ):
                    continue
                    
                if self.findTableAttribute(line):
                    continue
            
    def CreateMarkdown( self ):
        # 打开Markdown文件
        print('=========')
        with open( self.strMDFile, 'w',encoding='UTF-8' ) as objFile:
            objFile.write( '[TOC]' + '\n' ) # 第一行是自动目录
            iDB = 1 # 章节号的章, iDB.iTable
            for strDB,dicTableInfo in self.dicDB.items():
                strDBTitle,strDBComment = self.ProcessDBComment(strDB) # 从db.ini获得数据的Title和注释
                objFile.write( '# {iDB} {strDB} {strDBTitle}\n'.format(iDB=iDB, strDB=strDB, strDBTitle=strDBTitle) ) # # 1.DB1 数据库1
                objFile.write( strDBComment )                                                                         # 这里是数据库的注释
                iTable = 1  # 章节号的节, iDB.iTable
                for strTable,strTableAttribute in dicTableInfo.items():
                    strTableAttribute = self.ProcessTableAttribute(strTableAttribute) # 获得表的注释
                    strTableTitle = self.GetTableTitle(strTableAttribute)
                    print('=========')
                    objFile.write( '## {iDB}.{iTable} {strTable} {strTableTitle}\n'.format(iDB=iDB, iTable=iTable, strTableTitle=strTableTitle, strTable=strTable)  )
                    objFile.write( strTableAttribute + '\n'  )
                    objFile.write( '|字段|属性|注释|' + '\n'  )
                    objFile.write( '| ---- | ---- | ---- |' + '\n'  )
                    for strField,strFieldAttribute in self.dicTable[strDB+'_'+strTable].items():
                        strFieldAttribute,strComment = self.ProcessFieldAttribute(strFieldAttribute)
                        objFile.write( '| '+strField+' | '+strFieldAttribute+' | '+strComment+' |' + '\n'  )
                    iTable += 1
                iDB += 1
          
    # 从db.ini获得数据库的Title和注释
    def ProcessDBComment( self, strDB ):
        ini = configparser.ConfigParser()
        ini.read('db.ini')
        if strDB not in ini.sections():
            return strDB, ''
        strTitle = ''
        strComment = ''
        if 'Title'  in ini[strDB]:
            strTitle = ini.get( strDB, 'Title', )
        if 'Comment'  in ini[strDB]:
            strComment = ini.get( strDB, 'Comment' )
        return strTitle, strComment+'\n'
                
    def ProcessTableAttribute( self, strTableAttribute ):
        strComment = ''
        strDeleteMe = ''
        objMatch = re.search( re.compile("COMMENT='(.*)'"), strTableAttribute )
        if objMatch:
            strComment = objMatch.group(1)
            strDeleteMe = objMatch.group(0)
            
        strResult = strTableAttribute.replace( strDeleteMe, '' )
        strResult = strResult.replace(' ','\n')
        strResult = strResult.replace('DEFAULT\nCHARSET','DEFAULT CHARSET') # DEFAULT CHARSET
        if 0<len(strComment):
            strResult += (strDeleteMe)
        return strResult

    def ProcessFieldAttribute( self, strFieldAttribute ):
        strComment = ''
        strDeleteMe = ''
        objMatch = re.search( re.compile("COMMENT\s'(.*)'"), strFieldAttribute )
        if objMatch:
            strComment = objMatch.group(1)
            strDeleteMe = objMatch.group(0)
        
        strResult = strFieldAttribute.replace( strDeleteMe, '' )
        strResult = strResult.replace(' ',',<br>')
        strResult = strResult.replace('NOT,<br>NULL','NOT NULL') # NOT NULL
        strResult = strResult.replace('DEFAULT,<br>','DEFAULT ') # DEFAULT ''
        strResult = strResult.replace('CHARACTER,<br>SET,<br>','CHARACTER SET ') # CHARACTER SET latin1 
        strResult = strResult.replace('COLLATE,<br>','COLLATE ') # COLLATE latin1_general_cs 
        return strResult,strComment
        
    def GetTableTitle(self, strTableAttribute):
        if "'["!=strTableAttribute[0:2]:
            return ''
            
        if "]'" not in strTableAttribute:
            return ''
        
        print('到这里了吗?')
        strTableTitle = strTableAttribute[1:strTableAttribute.index(']')]
        strTableAttribute = strTableAttribute[strTableAttribute.index(']'):]
        
def main():
    '''
    proc = CSql2Markdown()
    proc.ProcessSQLFile()
    proc.CreateMarkdown()
    '''
    Const.IniFile = 'db.ini'

    objCmdLine = CCmdLine()
    dicCmdLine = objCmdLine.ProcessArg()
    #print(dicCmdLine)
    if 0 != len(dicCmdLine[Const.ERR_MSG]):
        print(dicCmdLine[Const.ERR_MSG])
        return 

    strMarkdownFile = 'MySQL2Markdown.md'
    dicDB = {}    # 格式: {数据库名:{表:表属性}}
    dicTable = {} # 格式: {数据库_表名:{字段:字段属性}}

    if Const.FILE not in dicCmdLine:
        dicCmdLine[Const.FILE] = 'MySQL2Markdown.sql'
        #strMarkdownFile = 'MySQL2Markdown.md'
        objMysql = CMySQL(dicCmdLine)
        objMysql.MySQL2SQL(dicCmdLine[Const.FILE])
        

    objSQLFile = CSQLFile(dicCmdLine[Const.FILE])
    objSQLFile.ProcessSQLFile()
    dicDB = objSQLFile.dicDB
    dicTable = objSQLFile.dicTable
    strMarkdownFile = dicCmdLine[Const.FILE] + '.md'
    objIni = CIniFile(Const.IniFile)
    objMarkdown = CMarkdown( strMarkdownFile, dicDB, dicTable, objIni )
    objMarkdown.CreateMarkdown()
    return 

    
if __name__ == '__main__':
    main()